""" Todo:
    1.commit changes when connection closes
    2.multiple label search
    3.make indexes on all tables!!!
"""
import sqlite3

class sqliteDB():
    """
    This class represents database
    """
    def __init__(self, dbFilePath):
        """
        Constructor.
        
        dbFilePath -- sqlite file path
        """
        self._dbFilePath = dbFilePath
    def openConnection(self):
        """
        open database connection
        """
        self._connection=sqlite3.connect(self._dbFilePath)
        self._connection.row_factory = sqlite3.Row
        self._c = self._connection.cursor()

    def closeConnection(self):
        """
        close database connection
        """
        self._connection.close()

    def initDB(self):
        """
        initiate database
        """
        if self.__testTable() == 0:
            self._c.executescript("
            CREATE TABLE `type` (
                `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                `name` VARCHAR(45) NOT NULL UNIQUE
                );

            CREATE TABLE `group` (
                `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                `name` VARCHAR(45) NOT NULL,
                `parentId` INTEGER NOT NULL
                );

            CREATE TABLE `records` (
              `id` INTEGER PRIMARY KEY AUTOINCREMENT,
              `parentId` INTEGER NOT NULL,
              `name` VARCHAR(45) NOT NULL,
              `url` VARCHAR(45) NOT NULL,
              `mainDiscription` TEXT NOT NULL,
              `mainVersionId` INTEGER 
            );

            CREATE TABLE `version` (
                `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                `parentId` INTEGER not null ,
                `name` VARCHAR(15) not null,
                `discription` TEXT,
                `path` TEXT not null
                );


            CREATE TABLE `label` (
                `id` INTEGER PRIMARY KEY AUTOINCREMENT,
                `name` VARCHAR(45) NOT NULL UNIQUE,
                `parentId` INTEGER NOT NULL);

            CREATE unique index index1 on `group` (name,parentId);
            CREATE unique index index2 on `records` (name,parentId);
            CREATE unique index index3 on `version` (name,parentId);
            CREATE unique index index4 on `label` (name,parentId);
            ")

    def insertNewVersion(self, entryInfo):
        """
        insert new version
        
        entryInfo -- verion info 
        """
        try:
            self._c.execute("""
            insert into `version` (`parentId`,`name`,`discription`,`path`)
            values (?,?,?,?)
            """,entryInfo)
        except Exception as err:
            print("version exists",err)
        else:
            #self.__commitChanges()
            return self._c.lastrowid


    def insertMain(self,entryInfo):
        """
        insert main record
        """
        try:
            self._c.execute("""
            insert into `records`
            (`parentId`,`name`,`url`,`mainDiscription`)
            values (?,?,?,?)
            """,entryInfo[:4])
        except Exception as err:
            tmpId=self.findIdbyName(entryInfo[1])
            print("entry exist",entryInfo[1],err)
            return
        oldRow=self._c.lastrowid
        self.makeMainVersion(oldRow,self.insertNewVersion((oldRow,)+entryInfo[5:]))
        #self.__commitChanges()
        

    def makeMainVersion(self, recordId, versionId):
        """
        make main version
        recordId -- id of record 
        versionId -- id of version 
        """
        self._c.execute("update `records` set `mainVersionId` = ? where `id` = ?",(versionId,recordId))

    def printAll(self):
        """
        select records
        """
        print("\n"*10)
        self._c.execute("select * from `type`")
        types=self._c.fetchall()
        for typeP in types:
            for shit in typeP.keys() : print("type:",shit,"=>",typeP[shit])
            self._c.execute("SELECT * from `group` where parentId=?",(typeP['id'],))
            groups=self._c.fetchall()
            for group in groups:
                for shit in group.keys() : print(" "*2,"group:",shit,"=>",group[shit])
                self._c.execute("SELECT * from `records` where parentId=?",(group['id'],))
                records=self._c.fetchall()
                for record in records:
                    for shit in record.keys() : print(" "*4,"entry:",shit,"=>",record[shit])
                    self._c.execute("SELECT * from `version` where parentId=?",(record['id'],))
                    versions=self._c.fetchall()
                    for version in versions:
                        for shit in version.keys() : print(" "*8,"version:",shit,"=>",version[shit])
                        

    def listAllInType(self,tableType='type'):
        """
        list all types
        """
        outList=[]
        self._c.execute("SELECT * from `%s`"%tableType)
        for row in self._c:
            outListRow={}
            for key in row.keys():
                outListRow[key]=row[key]
            outList.append(outListRow)
        return outList

    def ListAll(self,table,restrain,refer='*',backWard=False):
        """
        list a table content

        refer -- which column
        table -- which table
        """
        outList=[]
        if backWard:
            self._c.execute("select distinct %s from `%s` where Id = ? " %(refer,table),(restrain,))
        else:
            if table == 'type':
                self._c.execute("select distinct * from `type`")
            else:
                self._c.execute("select distinct %s from `%s` where parentId = ? " %(refer,table),(restrain,))
        for row in self._c:
            outListRow={}
            for key in row.keys():
                outListRow[key]=row[key]
            outList+=[outListRow]
        return outList

    def delEntry(self, id):
        """
        delete entry from db
        
        id -- id
        """
        self._c.execute("delete from records where id=?",(id,))
        self._c.execute("delete from version where parentId=?",(id,))

    def __testTable(self ):
        """
        testTables
        
        tableName -- table name
        """
        self._c.execute("""
        SELECT name FROM sqlite_master WHERE type='table';
        """)
        return l

    def __commitChanges(self):
        """
        commit db changes
        """
        self._connection.commit()

    def searchRecordByName(self, name):
        """
        search entry by name
        
        name -- entry name
        """
        self._c.execute("select * from `records` where name like ?",(name.lstrip()+"%",))
        result= self._c.fetchall()
        return result

    def searchRecordByLabels(self, labels):
        """
        search entry by label
        
        label -- entry label
        """
        labelList=labels.split()
        
        condition=('`label`.`name` like ? OR' * len(labelList))[:-2]
        self._c.execute("select `label`.`parentId` from `label` where "+condition,tuple(labelList))
        result= self._c.fetchall()
        out=[]
        #yes I know list generator can do this..ugly..
        for item in result:
            for record in self.ListAll('version',item['parentId'],backWard=True) :
                row=(self.ListAll('records',record['parentId'],backWard=True))[0]
                row['version']=record
                out+=[row]
        return out

    def getDefaultVersion(self, recordId):
        """
        get default version of record
        
        recordId -- id of record
        """
        self._c.execute("select `version`.* from `version`,`records` where  `version`.`id`=`records`.`mainVersionId` and `records`.`id` = ?", (recordId,))
        return dict(self._c.fetchone())
    
    def insertNewLabels(self, labels, parentId):
        """
        add labels into label
        
        labels -- list of labels
        parentId -- id of version
        """
        try:
            for label in labels.split():
                self._c.execute("insert into `label`(`name`,`parentId`) values (?,?)",(label,parentId))
        except sqlite3.Error as err:
            print (err)

    def listByVersion(self, entryName):
        """
        list by entry version
        
        entryName -- name of the entry
        """
        self._c.execute("SELECT * from `version` where parentId = ?",(self.findIdbyName(entryName),))
        l= self._c.fetchall()
        if len(l)>0:
            return l
        else:
            return False

        
    def findIdbyName(self, name):
        """
        find entry id by name
        
        name -- entry name
        """
        self._c.execute("select id from `records` where name = ?",(name,))
        l = self._c.fetchall()
        if len(l)>0:
            return l[0][0]
        else:
            return False

    def getEntryInfo(self, id):
        """
        get full entry info
        
        id -- id
        """
        self._c.execute("select * from `records` where id = ?",(id,))
        l = self._c.fetchall()
        if len(l)>0:
            return l[0]
        else:
            return False
    def insertNewType(self, typeName):
        """
        insert new type in to type table
        
        typeName -- name of type
        """
        try:
            self._c.execute("""
            insert into `type`
            (`name`) values (?)
            """,(typeName,))
            #self.__commitChanges()
        except Exception as err:
            print("type exists")

    def insertNewGroup(self, groupName, parentId):
        """
        insert new group in to group table
        
        groupName -- name of group
        parentId -- id of type group is gonna be in 
        """
        try:
            self._c.execute("""insert into `group` (`name`,`parentId`) values (?,?)
                """,(groupName,parentId))
            #self.__commitChanges()
        except Exception as err:
            print("group exists")
    def getIdbyName(self, name, table):
        """
        get id of group
        
        name -- which name
        """
        self._c.execute("select id from `%s` where  `name` =?" % table, (name,))
        return (self._c.fetchone())['id']
    def getTypeId(self,name):
        """
        get id of type
        
        name -- which type
        """
        self._c.execute("select id from `type` where  `name` =?", (name,))
        for out in self._c:
            return (out['id'])
    def getTypeByRecordName(self, recordName):
        """
        getType By Record Name
        
        recordName -- name of record
        """
        self._c.execute("select distinct `type`.* from `type`,`group`,`records` where  `type`.`id` = `group`.`parentId` and `group`.`id`=`records`.`parentId` and `records`.`name` = ?", (recordName,))
        return dict(self._c.fetchone())
    def getGroupByRecordName(self, recordName):
        """
        get group By Record Name
        
        recordName -- name of record
        """
        self._c.execute("select distinct `group`.* from `group`,`records` where  `group`.`id`=`records`.`parentId` and `records`.`name` = ?", (recordName,))
        return dict(self._c.fetchone())

    def comitChanges(self):
        """
        commit
        """
        self.__commitChanges()
        print("saved")
        
if __name__ ==  "__main__":
    testDb=sqliteDB("test.sqlite")
    testDb.openConnection()
    testDb.initDB()
    testDb.insertNewType("software")
    testDb.insertNewGroup("player",testDb.getIdbyName('software','type'))
    testDb.insertNewGroup("browser",testDb.getIdbyName('software','type'))
    testDb.insertMain((testDb.getIdbyName('player','group'),"foobar2k","http://mozila.org","perfect","d:\\foobar2k","1.0","1.0Version","d:\\foobar2000",))
    testDb.insertMain((testDb.getIdbyName('browser','group'),"Firefox","http://mozila.org","perfect","d:\\foobar2k","3.0","1.0Version","d:\\foobar2000",))
    testDb.insertNewType("multimedia")
    testDb.insertNewGroup("pictures",testDb.getIdbyName('multimedia','type'))
    testDb.insertMain((testDb.getIdbyName('pictures','group'),"4chan","http://4chan.org","perfect","d:\\foobar2k","2.0","1.0Version","d:\\foobar2000",))
    print("___________________")
    """
    for i in range(10):
        print("|||||||||||||||||||")
        testDb.insertMain((testDb.getIdbyName('software','type'),'name'+str(i),'url','discMain','path',str(i),'verD'+str(i),'path2'))
        for n in range(5):
            print("+++++++++++++++++++")
            testDb.insertNewVersion((testDb.getIdbyName('name'+str(i),'records'),'ver'+str(n),'discV'+str(n),'path3'))
    """
#   testDb.insertNewVersion((1,"vs2df","df","path"))
    #print(testDb.searchRecordByName("me"))
    #print(testDb.searchByLabel("google"))
    testDb.comitChanges()

    #print(testDb.listBy("type","records"))
    #print(testDb.ListAll('records',1))
    testDb.printAll()

#    testDb.listAll()
#    print("1111111",testDb.findIdbyName("name2"))
#    for version in testDb.listByVersion("name2"):
#        print(version)
#    print(testDb.getEntryInfo(1))
#    testDb=sqlite3.connect("test.sqlite")
#    add = testDb.cursor()
#    add.execute("select distinct `type`.`name` from `type`,`group`,`records` where  `type`.`id` = `group`.`parentId` and `group`.`parentId`=`records`.`parentId` and `records`.`name` = ?", ('foobar2k',))
#    for i in add:
#        print(i)
    #print(testDb.getDefaultVersion(testDb.getIdbyName('4chan','records')))
    #print((testDb.getTypeByRecordName('4chan')))
    testDb.insertNewLabels('test1 foobar',2)
    testDb.insertNewLabels('as shishishi',10)
    testDb.insertNewLabels('test shit good',1)
    testDb.comitChanges()
    out=(testDb.searchRecordByLabels('test test1 as'))
    for item in out:
        print(item['name']," version :",item['version']['name'])
    print(testDb.listAllInType('label'))

